package com.reef.util;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.List;

import javax.servlet.http.HttpServletRequest;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;

import com.reef.entity.Book;
import com.reef.entity.Result;
import com.reef.service.BookService;
@RestController
public class BookExcelUtils {
	@Autowired
   private BookService bookService;
	//导入
		@RequestMapping(value= "/importDataurl",method = RequestMethod.POST)
		public Object importData(MultipartFile file,HttpServletRequest servletRequest) {
			//获取文件名字
	     String FileName = file.getOriginalFilename();
	     //上传文件之后的名字
	     String newFileName = System.currentTimeMillis()+FileName;
	     //获取上传文件的位置
	     String path = servletRequest.getServletContext().getRealPath("/upload");
	     //拼接路径
	     File tempFile = new File(path+File.separator+newFileName);
	     try {
	    	 // 上传
			file.transferTo(tempFile);
			//1.读取文件
			FileInputStream in = new FileInputStream(tempFile);
			//2.通过poi解析流HSSFWorkbook处理流得到的对象，就是封装的excel文件所有的数据
			XSSFWorkbook workBook=new XSSFWorkbook(in);
			
			//3.获取excel文件里面的工作表sheet
			XSSFSheet  sheet = workBook.getSheetAt(0);
			//4.获取工作表数据，从第二行数据开始读取，读到最后一行结束getLastRowNum()
			int lastRowNum = sheet.getLastRowNum();
			for(int i = 1;i<=lastRowNum;i++) {
				//获取每一行
				XSSFRow row = sheet.getRow(i);
				//获取每一行里面的每个单元格的元素
				
				int bookId = Integer.valueOf((int)row.getCell(0).getNumericCellValue());//获取图书id				
				String name = row.getCell(1).getStringCellValue();//获取书名
				String author = row.getCell(2).getStringCellValue();//获取作者
				String publish = row.getCell(3).getStringCellValue();//获取出版社
				row.getCell(4).setCellType(Cell.CELL_TYPE_STRING);
				String isbn = row.getCell(4).getStringCellValue();//获取书号
				row.getCell(5).setCellType(Cell.CELL_TYPE_STRING);
				String introduction = row.getCell(5).getStringCellValue();//获取简介
				row.getCell(6).setCellType(Cell.CELL_TYPE_STRING);
				String language = row.getCell(6).getStringCellValue();//获取语言
				row.getCell(7).setCellType(Cell.CELL_TYPE_STRING);
				String price = row.getCell(7).getStringCellValue();//获取价格
			//	row.getCell(8).setCellType(Cell.CELL_TYPE_NUMERIC);
				String pubDate = row.getCell(8).getStringCellValue() ;//获取发布日期
				
				int classId = Integer.valueOf((int)row.getCell(9).getNumericCellValue());//获取图书类别编号
			
				int number = Integer.valueOf((int)row.getCell(10).getNumericCellValue());//获取数量
				
				int lendnum = Integer.valueOf((int)row.getCell(11).getNumericCellValue());	//获取被借阅次数		
				row.getCell(12).setCellType(Cell.CELL_TYPE_STRING);
				String position = row.getCell(12).getStringCellValue();//获取位置
			 //将数据封装到实体
				Book book = new Book(bookId,name,author,publish,isbn,introduction,language,price,pubDate,classId,number,lendnum,position); 
			 //插入到数据库
				bookService.save(book);
			}
		} catch (IllegalStateException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	     
	     return new Result("上传成功","",true);
		}
		//导出
		@RequestMapping("/exportData")
		public Object exportData() {
			List<Book> list = bookService.selAll();
			String[] titles = {"图书id","书名","作者","出版社","书号","简介","语言","价格","发布日期","图书类别编号","数量","被借阅次数","位置"};
			//1.创建文件对象  创建HSSFWorkbook 
			HSSFWorkbook  workBook = new HSSFWorkbook ();
			
			//2.创建工作表对象
			
			HSSFSheet  sheet = workBook.createSheet("book");
			//3。创建标题（第一行） 参数为下标  下标从零开始
			
			HSSFRow titlerow = sheet.createRow(0);
			for(int i = 0;i<titles.length;i++) {
				
				HSSFCell cell = titlerow.createCell(i);
				cell.setCellValue(titles[i]);
			}
			//5.将数据写入到excel
			for(int i =0;i<list.size();i++) {
				
				HSSFRow	row = sheet.createRow(i+1);
				row.createCell(0).setCellValue(list.get(i).getBookId());
				row.createCell(1).setCellValue(list.get(i).getName());
				row.createCell(2).setCellValue(list.get(i).getAuthor());
				row.createCell(3).setCellValue(list.get(i).getPublish());
				row.createCell(4).setCellValue(list.get(i).getIsbn());
				row.createCell(5).setCellValue(list.get(i).getIntroduction());
				row.createCell(6).setCellValue(list.get(i).getLanguage());
				row.createCell(7).setCellValue(list.get(i).getPrice());
				row.createCell(8).setCellValue(list.get(i).getPubDate());
				row.createCell(9).setCellValue(list.get(i).getClassId());
				row.createCell(10).setCellValue(list.get(i).getNumber());
				row.createCell(11).setCellValue(list.get(i).getLendnum());
				row.createCell(12).setCellValue(list.get(i).getPosition());
				
			}
			//文件 保存的地址
			try {
				workBook.write(new FileOutputStream("d:/book.xls"));
			} catch (FileNotFoundException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} catch (IOException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			return new Result("导出成功","",true);
		}
}
